﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace E4RPTools
{
    public class SqlDataAccess : IDisposable
    {
        #region Private Methods

        private static void AddParameters(SqlCommand command, IEnumerable<SqlParameter> parameters)
        {
            Debug.Assert(command != null, "command");
            Debug.Assert(parameters != null, "parameters");

            command.Parameters.Clear();
            foreach (SqlParameter parameter in parameters)
                command.Parameters.Add(parameter);
        }

        private static void PrepareCommand(SqlCommand command, string commandText, IEnumerable<SqlParameter> parameters)
        {
            command.CommandText = commandText;
            AddParameters(command, parameters);
        }

        private delegate T ValueIfNull<T>();

        private static T SafeConvert<T>(object value)
        {
            if (typeof(T).IsGenericType && typeof(T).GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                var converter = new NullableConverter(typeof(T));
                object valueAsUnderlyingType = Convert.ChangeType(value, converter.UnderlyingType);
                return (T)converter.ConvertFrom(valueAsUnderlyingType);
            }
            return (T)Convert.ChangeType(value, typeof(T));
        }

        private static T ExecuteScalar<T>(SqlTransaction sqlTransaction, ValueIfNull<T> getValueIfNull, string commandText, params SqlParameter[] parameters)
        {
            object value = ExecuteScalarInternal(sqlTransaction, commandText, parameters);
            if (value == null || Convert.IsDBNull(value))
                return getValueIfNull();
            return SafeConvert<T>(value);
        }

        private static object ExecuteScalarInternal(SqlTransaction sqlTransaction, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command = sqlTransaction.Connection.CreateCommand();
            command.Transaction = sqlTransaction;
            PrepareCommand(command, commandText, parameters);
            try
            {
                return command.ExecuteScalar();
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

        private static void OpenConnectionIfNeeded(SqlTransaction sqlTransaction)
        {
            if (sqlTransaction.Connection.State == ConnectionState.Closed)
                sqlTransaction.Connection.Open();
        }

        #endregion

        public void Fill(DataTable table, string connectionString, string query, params SqlParameter[] parameters)
        {
            Debug.Assert(table != null, "table");
            Debug.Assert(query != String.Empty, "query", "'query' argument cannot be empty.");

            var connection = new SqlConnection(connectionString);
            SqlCommand selectCommand = connection.CreateCommand();
            var adapter = new SqlDataAdapter { SelectCommand = selectCommand };
            PrepareCommand(selectCommand, query, parameters);
            adapter.Fill(table);
            selectCommand.Dispose();
            adapter.Dispose();
        }

        public Collection<string> ExecuteStringList(string connectionString, string commandText, params SqlParameter[] parameters)
        {
            var list = new Collection<string>();
            var connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            PrepareCommand(command, commandText, parameters);
            connection.Open();
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader != null)
                        while (reader.Read())
                            list.Add(reader.GetString(0));
                }
                return list;
            }
            finally
            {
                connection.Close();
            }
        }

        public void ExecuteNonQuery(SqlTransaction sqlTransaction, string commandText, params SqlParameter[] parameters)
        {
            OpenConnectionIfNeeded(sqlTransaction);
            SqlCommand command = sqlTransaction.Connection.CreateCommand();
            command.Transaction = sqlTransaction;
            PrepareCommand(command, commandText, parameters);
            command.ExecuteNonQuery();
        }

        public T ExecuteScalar<T>(SqlTransaction sqlTransaction, T nullValue, string commandText, params SqlParameter[] parameters)
        {
            OpenConnectionIfNeeded(sqlTransaction);
            return ExecuteScalar<T>(sqlTransaction, delegate { return nullValue; }, commandText, parameters);
        }

        public void Fill(SqlTransaction sqlTransaction, DataTable table, string query, params SqlParameter[] parameters)
        {
            Debug.Assert(table != null, "table");
            Debug.Assert(query != String.Empty, "query", "'query' argument cannot be empty.");

            OpenConnectionIfNeeded(sqlTransaction);
            SqlCommand command = sqlTransaction.Connection.CreateCommand();
            command.Transaction = sqlTransaction;
            var adapter = new SqlDataAdapter { SelectCommand = command };
            PrepareCommand(command, query, parameters);
            adapter.Fill(table);
            command.Dispose();
            adapter.Dispose();
        }

        public SqlCommand PrepareCommand(SqlTransaction sqlTransaction, string commandText, params SqlParameter[] parameters)
        {
            OpenConnectionIfNeeded(sqlTransaction);
            SqlCommand command = sqlTransaction.Connection.CreateCommand();
            command.Transaction = sqlTransaction;
            PrepareCommand(command, commandText, parameters);
            return command;
        }

        public void Update<TDataTable>(SqlTransaction sqlTransaction, TDataTable table, SqlCommand insertCommand, SqlCommand updateCommand,
            SqlCommand deleteCommand) where TDataTable : DataTable
        {
            Debug.Assert(table != null, "table");
            OpenConnectionIfNeeded(sqlTransaction);
            var adapter = new SqlDataAdapter();
            if (insertCommand != null)
                adapter.InsertCommand = insertCommand;
            if (updateCommand != null)
                adapter.UpdateCommand = updateCommand;
            if (deleteCommand != null)
                adapter.DeleteCommand = deleteCommand;
            adapter.Update(table);
            if (insertCommand != null)
                insertCommand.Dispose();
            if (updateCommand != null)
                updateCommand.Dispose();
            if (deleteCommand != null)
                deleteCommand.Dispose();
            adapter.Dispose();
        }


        public Collection<string> ExecuteStringList(SqlTransaction sqlTransaction, string commandText, params SqlParameter[] parameters)
        {
            var list = new Collection<string>();
            OpenConnectionIfNeeded(sqlTransaction);
            SqlCommand command = sqlTransaction.Connection.CreateCommand();
            command.Transaction = sqlTransaction;
            PrepareCommand(command, commandText, parameters);
            using (var reader = command.ExecuteReader())
                if (reader != null)
                    while (reader.Read())
                        list.Add(reader.GetString(0));
            return list;
        }

        public T[] ExecuteArray<T>(SqlTransaction sqlTransaction, string commandText, params SqlParameter[] parameters)
        {
            var resultList = new List<T>();
            OpenConnectionIfNeeded(sqlTransaction);
            SqlCommand command = sqlTransaction.Connection.CreateCommand();
            command.Transaction = sqlTransaction;
            PrepareCommand(command, commandText, parameters);
            using (var reader = command.ExecuteReader())
                if (reader != null)
                    while (reader.Read())
                    {
                        object value = reader.GetValue(0);
                        resultList.Add(SafeConvert<T>(value));
                    }
            return resultList.ToArray();
        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }
    }
}
